# 需导入pymysql，自行下载包
import threading

import pymysql
from dbutils.pooled_db import PooledDB

# 导入config包中导入config，py文件中对数据库进行的配置
from config.config import MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWD, MYSQL_DB


class MysqlDb():
    """
    封装mysql数据库操作
    host    链接
    port    端口
    user    用户
    passwd  密码
    db      数据库名称
    """

    def __init__(self, host, port, user, passwd, db):
        # 建立数据库连接
        self.pool = PooledDB(
            creator=pymysql,
            maxconnections=300,
            mincached=2,
            maxcached=5,
            maxshared=3,
            maxusage=None,
            blocking=True,
            ping=0,
            host=host,
            port=int(port),
            user=user,
            password=passwd,
            database=db
        )

    def select_db(self, sql, s_value=None, as_dict=True):
        """查询"""
        self.conn = self.pool.connection()
        self.cur = self.conn.cursor()
        try:
            # 检查连接是否断开，如果断开就进行重连
            self.conn.ping(reconnect=True)
            # 使用 execute() 执行sql
            lock = threading.Lock()
            lock.acquire()
            if not s_value:
                self.cur.execute(sql)
            else:
                self.cur.execute(sql, s_value)
            # 使用 fetchall() 获取查询结果
            data = self.cur.fetchall()
            lock.release()
            if data:
                if as_dict:
                    fields = [tup[0] for tup in self.cur._cursor.description]
                    return [dict(zip(fields, row)) for row in data]
                return data
            return data
        except (Exception) as e:
            print('sql:[{}]meet error'.format(sql))
            print(f"value:{s_value}")
            print(e.args[-1])
            return ()
        finally:
            if self.cur:
                self.cur.close()
            if self.conn:
                self.conn.close()

    def execute_db(self, sql, s_value=None):
        """更新/新增/删除"""
        self.conn = self.pool.connection()
        self.cur = self.conn.cursor()
        try:
            # 检查连接是否断开，如果断开就进行重连
            self.conn.ping(reconnect=True)
            # 使用 execute() 执行sql
            lock = threading.Lock()
            lock.acquire()
            if not s_value:
                self.cur.execute(sql)
            else:
                self.cur.execute(sql, s_value)
            lock.release()
            # 提交事务
            self.conn.commit()
            return "插入成功"
        except Exception as e:
            # 回滚所有更改
            print('[{}]meet error'.format(sql))
            print(e.args[-1])
            self.conn.rollback()
            return "操作出现错误"
        finally:
            if self.cur:
                self.cur.close()
            if self.conn:
                self.conn.close()


# 定义一个实例对象，方便别的文件引用其方法
db = MysqlDb(MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWD, MYSQL_DB)
